package com.adminpanel.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;


import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.adminpanel.modelbean.LogCount;
import com.adminpanel.modelbean.UserfileBean;



public class LogServices {

	private JdbcTemplate jdbcTemplate;
	private DataSource dataSource;
	
	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	
	public void addLog(final int user_id,final String action_name,final String create_datetime){
		jdbcTemplate.update("insert into adminpanel_log values(?,?,?,?)",
				new PreparedStatementSetter() {

					//@SuppressWarnings("static-access")
					@Override
					public void setValues(PreparedStatement ps) {
						
						try {
							ps.setInt(1, 0);
							ps.setInt(2, user_id);
							ps.setString(3, action_name);
							ps.setString(4, create_datetime);
							
						} catch (SQLException e) {
							

							e.printStackTrace();
						}
					}

				});
	}
	
	public String getLastRecord(int user_id){
		//String sql = "SELECT action_time from adminpanel_log where id=(SELECT LAST_INSERT_ID(id) as last_id from adminpanel_log where user_id ="+ user_id +" order by id DESC LIMIT 1)";
		String sql ="SELECT action_time from adminpanel_log where id <(SELECT LAST_INSERT_ID(id) as lastid  from adminpanel_log where user_id="+user_id+" ORDER BY id desc LIMIT 1) ORDER BY id desc LIMIT 1";
		return  jdbcTemplate.queryForObject(sql,new Object[]{}, java.lang.String.class);
	}
	
	public List<LogCount> getRc(int user_id,String fd,String td){
		//String sql = "select DATE_FORMAT(action_time,'%Y-%m-%d') as created_day,COUNT(*) as counts from (select * FROM adminpanel_log where user_id='"+user_id+"'  and action_time BETWEEN '"+fd+"' and '"+td+"%')as aa GROUP BY created_day";
		String sql = "select DATE_FORMAT(action_time,'%Y-%m-%d') as created_day,COUNT(*) as counts from (select * FROM adminpanel_log where user_id = ?  and action_time BETWEEN ? and ?'%')as aa GROUP BY created_day";
		//System.out.println(sql);
		RowMapper<LogCount> mapper = new RowMapper<LogCount>() {

			@Override
			public LogCount mapRow(ResultSet rs, int rowNum)throws SQLException {

				LogCount lc = new LogCount();
				lc.setCreate_day(rs.getString("created_day"));
				lc.setCounts(rs.getString("counts"));
				return lc;

			}

		};
		return jdbcTemplate.query(sql, mapper, new Object[] {user_id,fd,td});
		
	}
}
